USE [db_flower]
GO

/****** Object:  StoredProcedure [dbo].[sp_categories_select_all]    Script Date: 05/26/2012 16:04:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sp_categories_select_all]
AS
BEGIN
	select Categories.categoryid, Categories.categoryname, Categories.parentid, Categories.status
		from Categories
		where Categories.parentid <> 0
END


GO

/****** Object:  StoredProcedure [dbo].[sp_categories_select_all_root]    Script Date: 05/26/2012 16:04:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sp_categories_select_all_root]
AS
BEGIN
	select Categories.categoryid, Categories.categoryname, Categories.parentid, Categories.status
		from Categories
		where Categories.parentid = 0
END


GO

/****** Object:  StoredProcedure [dbo].[sp_customers_insert]    Script Date: 05/26/2012 16:04:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[sp_customers_insert]
			@customername nvarchar(50)
           ,@birthday datetime
           ,@address nvarchar(500)
           ,@phone char(15)
           ,@username varchar(20)
           ,@password varchar(64)
           ,@email varchar(50)
           ,@note nvarchar(500)
as
INSERT INTO [db_flower].[dbo].[Customers]
           ([customername]
           ,[birthday]
           ,[address]
           ,[phone]
           ,[username]
           ,[password]
           ,[email]
           ,[role]
           ,[createdate]
           ,[note]
           ,[active]
           ,[ban])
     VALUES
           (@customername
           ,@birthday
           ,@address
           ,@phone
           ,@username
           ,@password
           ,@email
           ,0
           ,GETDATE()
           ,@note
           ,'true'
           ,'false')

GO

/****** Object:  StoredProcedure [dbo].[sp_login]    Script Date: 05/26/2012 16:04:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[sp_login]
	 @username varchar(20)
	,@password varchar(64)
as
	select Customers.username, Customers.customerid, Customers.customername, Customers.address, Customers.phone, Customers.email
		from Customers
		where username = @username and [password] = @password and role = 0
GO

/****** Object:  StoredProcedure [dbo].[sp_order_detail_insert]    Script Date: 05/26/2012 16:04:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create proc [dbo].[sp_order_detail_insert]
	@orderid int
   ,@productid int
   ,@quality int = null
   ,@note1 nvarchar(500) = null
as
INSERT INTO [db_flower].[dbo].[Order_Details]
           ([orderid]
           ,[productid]
           ,[quality]
           ,[note])
     VALUES (@orderid
           ,@productid
           ,@quality
           ,@note1)  
GO

/****** Object:  StoredProcedure [dbo].[sp_orders_insert]    Script Date: 05/26/2012 16:04:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[sp_orders_insert]
			@customerid int
           ,@deliverydate datetime = null
           ,@note nvarchar(500) = null
           ,@deliveryaddress nvarchar(500) = null
           ,@total float
           ,@totalpayment float
           ,@status int

AS
BEGIN
	BEGIN TRANSACTION order_insert
		INSERT INTO [db_flower].[dbo].[Orders]
				   ([customerid]
				   ,[createdate]
				   ,[deliverydate]
				   ,[note]
				   ,[deliveryaddress]
				   ,[total]
				   ,[totalpayment]
				   ,[status])
			 VALUES
				   (@customerid
				   ,GETDATE()
				   ,@deliverydate
				   ,@note
				   ,@deliveryaddress
				   ,@total
				   ,@totalpayment
				   ,@status)
				   
		
    IF @@ERROR != 0
        BEGIN
			ROLLBACK TRANSACTION order_insert
            RETURN
        END
    ELSE
    BEGIN
        COMMIT TRANSACTION
    END
	
           
END

GO

/****** Object:  StoredProcedure [dbo].[sp_product_description]    Script Date: 05/26/2012 16:04:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create PROCEDURE [dbo].[sp_product_description]
@productid int
AS
BEGIN
	SELECT [dbo].[Products].[description]
  FROM [db_flower].[dbo].[Products]
  WHERE [dbo].[Products].productid=@productid
END


GO

/****** Object:  StoredProcedure [dbo].[sp_select_username]    Script Date: 05/26/2012 16:04:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create proc [dbo].[sp_select_username]
@username varchar(20)
as
	select Customers.username 
		from Customers
		where username = @username
GO

/****** Object:  StoredProcedure [dbo].[sp_view_productview]    Script Date: 05/26/2012 16:04:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sp_view_productview]

AS
BEGIN
	SELECT [productid]
      ,[productname]
      ,[categoryid]
      ,[createdate]
      ,[modifydate]
      ,[unit]
      ,[image]
      ,[totalview]
      ,[totalsell]
      ,[isnew]
      ,[price]
      ,[quantity]
      ,[discount]
  FROM [db_flower].[dbo].[products_view]
  ORDER BY [db_flower].[dbo].[products_view].createdate DESC
END


GO

/****** Object:  StoredProcedure [dbo].[sp_view_productview_promotions]    Script Date: 05/26/2012 16:04:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create PROCEDURE [dbo].[sp_view_productview_promotions]

AS
BEGIN
	SELECT [productid]
      ,[productname]
      ,[categoryid]
      ,[createdate]
      ,[modifydate]
      ,[unit]
      ,[image]
      ,[totalview]
      ,[totalsell]
      ,[isnew]
      ,[price]
      ,[quantity]
      ,[discount]
  FROM [db_flower].[dbo].[product_view_promotions]
  ORDER BY [db_flower].[dbo].[product_view_promotions].createdate DESC
END


GO


CREATE VIEW [dbo].[product_view_promotions]
AS
SELECT     TOP (100) PERCENT dbo.Products.productid, dbo.Products.productname, dbo.Products.categoryid, dbo.Products.createdate, dbo.Products.modifydate, 
                      dbo.Products.unit, dbo.Products.image, dbo.Products.totalview, dbo.Products.totalsell, dbo.Products.isnew, dbo.Products.price, dbo.Products.quantity, 
                      dbo.Promotions.discount
FROM         dbo.Promotions INNER JOIN
                      dbo.Product_Promotion ON dbo.Promotions.promotionid = dbo.Product_Promotion.promotionid INNER JOIN
                      dbo.Products ON dbo.Product_Promotion.productid = dbo.Products.productid
WHERE     (dbo.Products.status = 1) AND (dbo.Product_Promotion.startdate <= GETDATE()) AND (dbo.Product_Promotion.enddate >= GETDATE())


GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[26] 4[29] 2[16] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Promotions"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 125
               Right = 201
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Product_Promotion"
            Begin Extent = 
               Top = 6
               Left = 239
               Bottom = 157
               Right = 399
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Products"
            Begin Extent = 
               Top = 6
               Left = 437
               Bottom = 276
               Right = 597
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 14
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'product_view_promotions'
GO

CREATE VIEW [dbo].[products_view]
AS
SELECT     dbo.Products.productid, dbo.Products.productname, dbo.Products.categoryid, dbo.Products.createdate, dbo.Products.modifydate, dbo.Products.unit, 
                      dbo.Products.image, dbo.Products.totalview, dbo.Products.totalsell, dbo.Products.isnew, dbo.Products.price, dbo.Products.quantity, dbo.Promotions.discount
FROM         dbo.Promotions INNER JOIN
                      dbo.Product_Promotion ON dbo.Promotions.promotionid = dbo.Product_Promotion.promotionid RIGHT OUTER JOIN
                      dbo.Products ON dbo.Product_Promotion.productid = dbo.Products.productid
WHERE     (dbo.Products.status = 1)


GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[33] 4[28] 2[10] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Products"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 275
               Right = 198
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Product_Promotion"
            Begin Extent = 
               Top = 9
               Left = 290
               Bottom = 181
               Right = 450
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Promotions"
            Begin Extent = 
               Top = 12
               Left = 542
               Bottom = 168
               Right = 705
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 14
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'products_view'
GO

